<?php

	include("pub/conn.php");
	include("pub/functions.php");

	$gymName = isset($_POST['gymName']) ? $_POST['gymName'] : '全国';
	$do = isset($_POST['do']) ? $_POST['do'] : '';

	$sql = array(
		// 各门店的会员情况，即‘用户画像’
		"getGymUsers" =>"
			SELECT g.sex, g.age, g.birthday, g.weight, g.height
			from tj.gym_users g
			where g.`gym_name` = '$gymName' or g.city = '$gymName' or '$gymName' = '全国'

		",

		"getGymUsers_zk" =>"
			SELECT w.sex, w.age, w.birthday, w.weight, w.height
			from (
				select distinct u.`name` as userName, u.sex, u.age, u.birthday, u.weight, u.height, o.gym_id
				from `user` u 
				inner join orders o on o.user_uuid = u.uuid
				inner join user_subject s on o.order_uuid = s.order_uuid
				inner join user_classes e on e.user_subject_uuid = s.uuid
				where o.`status` = 1 and e.`status` = 0 and o.all_times > 1 and o.gym_id > 0
			) as w 
			inner join gym g on g.id = w.gym_id 
			where g.`name` = '$gymName' or g.city = '$gymName' or '$gymName' = '全国'
		",


		// 各门店每日收入情况，上课数，用户数
		"getGymDays" => "
			SELECT g.`day`, sum(g.fee_ty) as fee_ty, sum(g.fee_zk) as fee_zk, sum(g.users_ty) as users_ty, 
				sum(g.users_zk) as users_zk, sum(g.classes) as classes, sum(g.users) as users
			from tj.gym_days g
			where g.`gym_name` = '$gymName' or g.city = '$gymName' or '$gymName' = '全国'
			group by g.`day`
		",

		// 各门店每日收入情况，细分全部，续课，正式课
		"getIncomes" => "
			SELECT date_format(o.create_time, '%Y-%m') as mon, 
			sum(o.pay_fee / 100) total_fee,
			sum(case when x.create_time is not null then o.pay_fee / 100 end) xk_fee,
			sum(case when z.create_time is not null then o.pay_fee / 100 end) zh_fee
			from orders o
			inner join gym g on g.id = o.gym_id
			left join tj.order_xk x on o.order_uuid = x.order_uuid
			left join tj.order_zh z on o.order_uuid = z.order_uuid
			where o.`status` = 1 and o.all_times > 1 and (g.`name` = '$gymName' or g.city = '$gymName' or '$gymName' = '全国')
			group by mon
		",

		// 各门店每月情况，细分用户数，新用户数，上课人数，上课人次，体验课上课人次
		"getGymMonths" => "
			SELECT date_format(m.`month`, '%Y-%m') as mon, 
				sum(m.usersCount) usersCount, 
				sum(m.newUsersCount) newUsersCount, 
				sum(m.classesUsersCount) classesUsersCount, 
				sum(m.classesCount) classesCount,
				sum(m.ty_classesCount) ty_classesCount
			from tj.gym_months m
			inner join gym g on g.id = m.gym_id
			where g.`status` = 1 and (g.`name` = '$gymName' or g.city = '$gymName' or '$gymName' = '全国')
			group by mon
		",

		"getGymRegions" => "
			SELECT r.id as regionId, r.`name` as regionName, rg.city_id as cityId, g.city as cityName, g.`name` as gymName, g.id as gymId
			from gym g
			inner join region_gym rg on rg.gym_id = g.id
			inner join region r on r.id = rg.region_id
			where g.status = 1
			order by r.id, g.city
		"
	);
	
	echo query_sql($sql[$do], $conn);

?>